﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using Ebada.Components;
using Ebada.Server;
using Itop.KSRY.Model;
using Itop.KSRY.BLL;
using DevExpress.Utils;
using System.Data;
using System.Collections;
namespace Itop.KSRY {
    public class DBHelper {
        public static Castle.Windsor.IWindsorContainer IOC;
        private static IBaseSqlMapDao sqlMapper;
        
        public static IBaseSqlMapDao SqlMapper {
            get {
                if (sqlMapper == null)
                    sqlMapper = IOC.GetService<IBaseSqlMapDao>();

                return sqlMapper;

            }
        }
        public static T GetService<T>() {
            return IOC.GetService<T>();
        }
         static DBHelper() {
            if (IOC == null) {
                new ServerContainer();
                IOC = ServerContainer.PlatformServer;
            }
            Create();
        }
        
        public static void Create() {
            string file =AppDomain.CurrentDomain.BaseDirectory+ "\\ksry.dbl";
            string table_sysset = "CREATE TABLE IF NOT EXISTS sysset([id] nvarchar(50),	"
                + "[dj0] integer , [dj1] integer ,	[dj2] integer ,	[dj3] integer ,	[dj4] integer ,	[dj5] integer ,	[nl] integer ,[lastks] boolean , primary key (id))";
            string table_ksman = "CREATE TABLE IF NOT EXISTS ksman "
                + "(id text ,orderid INTEGER,dwcode text,dwname text,xm text,zw text,nl integer,zy text,jb integer,ksbz text,remark text,lastbz integer,ksnum integer,primary key(id)  )";
            string idx_man = "CREATE UNIQUE INDEX if not exists [IDX_KSMAN_man] ON [ksman]([dwname]  ASC,[xm]  ASC)";
            //string table_ksman = "CREATE TABLE IF NOT EXISTS ksman "
            //    + "(id nvarchar(50) ,orderid INTEGER,dwcode nvarchar(50),dwname nvarchar(50),xm nvarchar(50),zw nvarchar(50),nl integer,zy nvarchar(50),jb integer,ksbz nvarchar(5),remarker nvarchar(50),primary key(id)  )";
            SqliteHelper.UpdateData(table_ksman);
            SqliteHelper.UpdateData(table_sysset);
            SqliteHelper.UpdateData(idx_man);
        }
        public static void ImportExcel() {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "*.excel|*.xls";
            dlg.RestoreDirectory = true;
            dlg.Multiselect = true;
            if (dlg.ShowDialog() == DialogResult.OK) {
                Application.DoEvents();
                WaitDialogForm waitdlg =new WaitDialogForm("","导入文件");
                foreach (string file in dlg.FileNames) {
                    waitdlg.SetCaption(Path.GetFileName(file));
                    ImportExcel(file);
                }
                waitdlg.Close();
            }
        }
        public static void Clearksman(){

            SqliteHelper.UpdateData("DELETE FROM KSMAN");
        }
        public static void ImportExcel(string file) {
            FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read);
            HSSFWorkbook workbook = new HSSFWorkbook(fs, true);
            HSSFSheet sheet = workbook.GetSheetAt(0);
            //查找序号
            
            int rownum = sheet.LastRowNum;
            int xhrownum=-1;
            int xhcolnum=-1;
            for (int i = 0; i < rownum; i++) {
                HSSFRow row = sheet.GetRow(i);
                if (row == null) continue;
                int k = row.LastCellNum;
                for (int j = 0; j < k; j++) {
                    try {
                        if (row.GetCell(j).ToString() == "序号") {
                            xhrownum = i;
                            xhcolnum = j;
                            break;
                        }
                    } catch { }
                }
                if (xhrownum >= 0) break;
            }
            if (xhrownum == -1) {
                MessageBox.Show("不是有效格式");
                return;
            }
            if(rownum<5){
                MessageBox.Show("不是有效格式");
                return;
            }
            int colnum = sheet.GetRow(xhrownum).LastCellNum;
            if (colnum < 9) {
                MessageBox.Show("不是有效格式2");
                return;
            }
            xhrownum++;
            ksmanBLL bll = DBHelper.GetService<ksmanBLL>();
            List<ksman> insertlist = new List<ksman>();
            for (int i = xhrownum; i < rownum; i++) {
                HSSFRow row = sheet.GetRow(i);
                if (row.LastCellNum < 9)
                    continue;
                ksman man = new ksman();
                man.id += i;
                Type t = man.GetType();
                int k = 0;
                for (int j = xhcolnum; j < 9+xhcolnum; j++) {
                    if (k == 0 || k == 4 || k == 6) {
                        try {
                            t.GetProperty(cols[k]).SetValue(man, (int)row.GetCell(j).NumericCellValue, null);
                        } catch { }
                    } else {
                        try {
                            t.GetProperty(cols[k]).SetValue(man, row.GetCell(j).ToString(), null);
                        } catch { }
                    }
                    k++;
                }
                if (string.IsNullOrEmpty(man.xm)) continue;
                try {
                    insertlist.Add(man);
                } catch { }
            }
            if (insertlist.Count > 0)
                DBHelper.SqlMapper.ExecuteTransationUpdate(insertlist, null, null);
        }
        public static void ExportExcel(IList<ksman> list) {
            FileStream fs = new FileStream(Application.StartupPath+"\\template.xls", FileMode.Open, FileAccess.Read);
            HSSFWorkbook workbook = new HSSFWorkbook(fs, true);
            HSSFSheet sheet = workbook.GetSheetAt(0);
            //查找序号
            DataTable dt = ConvertHelper.ToDataTable((IList)list);
            int k = 0;
            HSSFCellStyle[] styles = new HSSFCellStyle[cols2.Length];
            for (int i = 0; i < styles.Length; i++) {
                styles[i]= sheet.GetRow(1).GetCell(i).CellStyle;
            }
            while (k < 2) {
                sheet = workbook.GetSheetAt(k);
                DataRow[] rows = dt.Select("jb=0");
                if(k==1)
                    rows = dt.Select("jb>0","dwname,orderid");
                for (int i = 0; i < rows.Length; i++) {
                    DataRow row = rows[i];
                    HSSFRow hrow = sheet.CreateRow(2 + i);
                    
                    hrow.CreateCell(0).SetCellValue("" + (i + 1));
                    hrow.GetCell(0).CellStyle = styles[0];
                    for (int j = 1; j < cols2.Length; j++) {

                        hrow.CreateCell(j).SetCellValue(row[cols2[j]].ToString());
                        hrow.GetCell(j).CellStyle = styles[j];
                    }

                }
                k++;
            }
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "*.excel|*.xls";
            dlg.RestoreDirectory = true;
            if (dlg.ShowDialog() == DialogResult.OK) {
                FileStream f = new FileStream(dlg.FileName, FileMode.OpenOrCreate, FileAccess.Write);
                workbook.Write(f);
                f.Close();
                f.Dispose();
            }
        }
        static string[] cols = new string[] { "orderid", "dwname", "xm", "zw", "nl", "zy", "jb", "ksbz", "remark" };
        static string[] cols2 = new string[] { "orderid", "dwname", "xm","zw","zy" };
    }
}
